package com.dp.service.impl;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Query;

import org.springframework.stereotype.Service;


import com.dp.service.impl3.BaseImpl;
import com.dp.tj.TJname;
import com.dp.util.DataMap;
import com.dp.util.InterUtil;
import com.dp.vo.TSysAutoactivetimer;
import com.dp.vo.TTjAdsAccess;
import com.dp.vo.TTjChannelsAccess;
import com.dp.vo.TTjIncomingbyarea;
import com.dp.vo.TTjIncomingbycontent;
import com.dp.vo.TTjInoutuser;
import com.dp.vo.TTjK16LinkTime;
import com.dp.vo.TTjMobile;
import com.dp.vo.TTjNewUser;
import com.dp.vo.TTjPltfmActUser;
import com.dp.vo.TTjSp;
import com.dp.vo.TTjTopTwenty;
import com.dp.vo.TTjUsernumber;

@Service
public class ReportServiceImpl extends BaseImpl {
    public static String whoami;
    static{
    	whoami=""+System.currentTimeMillis();    	
    }

    /*  @SuppressWarnings("unchecked")
  public List<TSpcode> findAll() {
    	List<TSpcode> s=new ArrayList<TSpcode>();
    	try {
    	this.initEM();
    	          
        Query query = getEntityManager().createQuery("select p FROM TSpcode p");
       // em.createNativeQuery(arg0, arg1)
       // em.close();
        	s=query.getResultList();
    	} catch (Exception e) {
    		e.printStackTrace();
    	}finally{
    		this.closeAll();
    	}
        return s;
    }*/
    public String doTJ(TJname which) {
    	String t=null;
    	
    	this.sql="select "+which+"()" ;
    	System.out.println(sql);
    	try {
			t=this.getOneString();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return t;
    }
    public boolean findCanIdoJod() {
    	boolean canidojob=false;
    	try {    		
			
		int s=-1;
		boolean isme=false;
		
    	this.sql="select count(pvalue) from t_sys_sysparam where pfirstname='sys' and plastname='whenIdoJob' and  ADDTIME(pvalue,'0:10:0')>now()" ;
    	s=this.getOneInt();
    	if(s==0)
    	{
    		//上次更新10分钟内没有人更新，就认为没有人在运行,因为固定定时器是5分钟执行一次的
    		sql=("update t_sys_sysparam set pvalue=now() where pfirstname='sys' and plastname='whenIdoJob'");
    		this.em.createNativeQuery(sql).executeUpdate();
    		sql=("update t_sys_sysparam set pvalue='"+whoami+"' where pfirstname='sys' and plastname='whodoJob'"); 
    		this.em.createNativeQuery(sql).executeUpdate();
    		canidojob=true;
    	}
    	else
    	{
    		//上次更新10分钟内有人更新，就认为有人在运行，如果是是自己则需要继续更新时间，不是自己不需要更新时间
    		sql=("select pvalue from t_sys_sysparam where pfirstname='sys' and plastname='whodoJob'");
    		isme=(whoami.equals(this.getOneString()));
    		if(isme)
    			{
    			  sql=("update t_sys_sysparam set pvalue=now() where pfirstname='sys' and plastname='whenIdoJob'");
    			  this.em.createNativeQuery(sql).executeUpdate();
    			}
    		
    		canidojob=isme;
    	}
    	
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    
        return canidojob;
    }
    
    public List<TSysAutoactivetimer> findAllTimer() {
    	List<TSysAutoactivetimer> t=null;
    	try {
    		/*this.initEM();
    	em.getTransaction().begin();
    	em.clear();
        Query query = getEntityManager().createQuery("select p FROM TSysAutoactivetimer p");       
        t=query.getResultList(); 
        System.out.println("dgdfgdfg");
        em.flush();
        em.getTransaction().commit();
        */
    	//em.setFlushMode(FlushModeType.)
        t=new ArrayList<TSysAutoactivetimer>();
        TSysAutoactivetimer temp=null;
//    	
//			this.init();
//		
       
    	this.sql="select t.timerid,t.timertime,t.timermsg,t.timequeue,t.isvalid from t_sys_autoactivetimer t" ;
    	sql="select ts from TSysAutoactivetimer ts";
    	t=this.em.createQuery(sql).getResultList();
//    	this.rs1=stmt.executeQuery(sql);
//    	while(rs1.next())
//    	{
//    	   temp=new TSysAutoactivetimer();
//    	   temp.setTimerid(rs1.getString(1));
//    	   temp.setTimertime(rs1.getString(2));
//    	   temp.setTimermsg(rs1.getString(3));
//    	   temp.setIsvalid(rs1.getString(5));  
//    	   t.add(temp);
//    	}    	
        
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//    	finally{this.closeAll(); }
        return t;
    }
    public List<TTjIncomingbyarea> findIA(String year) {
    	List<TTjIncomingbyarea> t=new ArrayList<TTjIncomingbyarea>();
    	TTjIncomingbyarea temp=null;
    	try {
//			this.init();
		
    	this.sql="select upareaid,ifnull(upareaname,'notset') as upareaname , ifnull(pricetype,'notset') as pricetype ,cmonth,sum(incoming) as incoming from t_tj_incomingbyarea a" +
    			" where a.cyear='"+year+"' group by upareaid,upareaname,pricetype,cmonth " +
    			" order by  upareaid,upareaname,pricetype,cmonth ";
    	List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
		for(Object[]o:tempp)
		{
    	   temp=new TTjIncomingbyarea();
    	   temp.setUpareaname(this.getString(o[1]));
    	   temp.setPricetype(this.getString(o[2]));
    	   temp.setCmonth(this.getString(o[3]));
    	   temp.setIncoming(this.getInteger(o[4]));  
    	   t.add(temp);
    	}    	
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//    	finally{this.closeAll(); }
        return t;
    }
    public List<TTjIncomingbycontent> findIC(String year) {
    	List<TTjIncomingbycontent> t=new ArrayList<TTjIncomingbycontent>();
    	TTjIncomingbycontent temp=null;
    	try {
	
    	this.sql=" select  ifnull(ctype,'notset') as ctype ,ifnull(pricetype,'notset') as pricetype,cmonth,sum(incoming) as incoming from t_tj_incomingbycontent" +
    			" where cyear='"+year+"' group by ctype,pricetype,cmonth    order by  ctype,pricetype,cmonth";
    	List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
		for(Object[]o:tempp)
		{
    	   temp=new TTjIncomingbycontent();
    	   temp.setCtype(this.getString(o[0]));
    	   temp.setPricetype(this.getString(o[1]));
    	   temp.setCmonth(this.getString(o[2]));
    	   temp.setIncoming(this.getInteger(o[3])); 
    	   t.add(temp);
    	}    	
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    
        return t;
    }
    public List<TTjSp> findSP(String... year) {
    	List<TTjSp> t=new ArrayList<TTjSp>();
    	TTjSp temp=null;
    	try {
		
		   switch(year.length)
		   {
		   case 3:
			   this.sql=" select  ifnull(spname,'notset') as spname  , ifnull(sname,'notset') as sname    ,chour,sum(downloadnumber) as dd,sum(ordernumber) as od from t_tj_sp " +
   			            "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' and cday='"+year[2]+"'  group by spname,sname,chour";
			   break;
		   case 2:
			   this.sql=" select  ifnull(spname,'notset') as spname  , ifnull(sname,'notset') as sname  ,cday,sum(downloadnumber) as dd,sum(ordernumber) as od from t_tj_sp " +
		            "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"'  group by spname,sname,cday";
			   break;
		   case 1:
			   this.sql=" select  ifnull(spname,'notset') as spname  , ifnull(sname,'notset') as sname  ,cmonth,sum(downloadnumber) as dd,sum(ordernumber) as od from t_tj_sp " +
	            "  where cyear='"+year[0]+"'  group by spname,sname,cmonth";		  
			   break;
		   default:
			   break;
		   
		   }
		   List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
    	   temp=new TTjSp();
    	   temp.setSpname(this.getString(o[0]));
    	   temp.setSname(this.getString(o[1]));
    	   temp.setCmonth(this.getString(o[2]));
    	   temp.setDownloadnumber(this.getInteger(o[3]));
    	   temp.setOrdernumber(this.getInteger(o[4]));
    	   t.add(temp);
    	}    	
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
        return t;
    }
    
    //----------------------------0618 k16 link time------------------------
    public List<TTjK16LinkTime> getTjForK16ConnectCount(String k16sn,String k16phonenumber,
    		String k16buytime1,String k16buytime2,String kdnumber,String... year)
    {
		List<TTjK16LinkTime> t=new ArrayList<TTjK16LinkTime>();
		TTjK16LinkTime temp=null;
    	try {
		//	this.init();
			switch(year.length)
			{
			   case 2:
				   this.sql="select * from (SELECT IFNULL(mencoding,'unknown'),telnumber,IFNULL(kdnumber,''),createtime,cday,sum(linktime) a FROM t_tj_k16linktime "
					   + " t where  cyear='"+year[0]+"' and cmonth='"+year[1]+"' ";
				   if(k16sn != null && !"".equals(k16sn))
					   this.sql += " and mencoding like '%"+k16sn+"%'";
				   if(k16phonenumber != null && !"".equals(k16phonenumber))
					   this.sql += " and telnumber like '%"+k16phonenumber+"%'";
				   if(k16buytime1 != null && !"".equals(k16buytime1))
					   this.sql += " and createtime > '"+k16buytime1+"'";
				   if(k16buytime2 != null && !"".equals(k16buytime2))
					   this.sql += " and createtime < '"+k16buytime2+"'";
				   //add kdnumber
				   if(kdnumber != null && !"".equals(kdnumber))
					   this.sql += " and kdnumber like '%"+kdnumber+"%'";
				   
//				   if(k16linkhour != null && !"".equals(k16linkhour))
//				   {	
//					   int m = Integer.parseInt(k16linkhour)*60;
//					   this.sql += " group by mencoding,kdnumber,cday ) a where a.a< "+m;
//					   break;
//				   }
				   this.sql += " group by mencoding,kdnumber,cday ) a";
				   break;
			   case 1:
				   this.sql=" SELECT IFNULL(mencoding,'unknown'),telnumber,IFNULL(kdnumber,''),createtime,cmonth,sum(linktime) a FROM t_tj_k16linktime t " +
			            "  where cyear='"+year[0]+"' ";
				   if(k16sn != null && !"".equals(k16sn))
					   this.sql += " and mencoding like '%"+k16sn+"%'";
				   if(k16phonenumber != null && !"".equals(k16phonenumber))
					   this.sql += " and telnumber like '%"+k16phonenumber+"%'";
				   if(k16buytime1 != null && !"".equals(k16buytime1))
					   this.sql += " and createtime > '"+k16buytime1+"'";
				   if(k16buytime2 != null && !"".equals(k16buytime2))
					   this.sql += " and createtime < '"+k16buytime2+"'";
				   //add kdnumber
				   if(kdnumber != null && !"".equals(kdnumber))
					   this.sql += " and kdnumber like '%"+kdnumber+"%'";
//				   if(k16linkhour != null && !"".equals(k16linkhour))
//				   {	
//					   int m = Integer.parseInt(k16linkhour)*60;
//					   this.sql += " group by mencoding,cmonth ) a where a.a< "+m;
//					   break;
//				   }
				   this.sql += " group by mencoding,cmonth ) a";
				   break;
			   default:
				   break;
			}
			logger.info("getTjForK16ConnectCount excute: " +sql);
			SimpleDateFormat   theDate   =   new   SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
	    	   temp=new TTjK16LinkTime();
	    	   temp.setMencoding(this.getString(o[0]));
	    	   temp.setTelnumber(this.getString(o[1]));
	    	   temp.setKdnumber(this.getString(o[2]));
	    	   temp.setCreatetime(o[3]==null?null:theDate.parse(this.getString(o[3])));
	    	   temp.setCmonth(this.getString(o[4]));
	    	   temp.setLinktime(this.getInteger(o[5]));
	    	   t.add(temp);
	    	}    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
    
        return t;
    }
    //----------------------------0317 platform user------------------------
    public List<TTjPltfmActUser> findTTjPltfmActUser(String... year) {
    	List<TTjPltfmActUser> t=new ArrayList<TTjPltfmActUser>();
    	TTjPltfmActUser temp=null;
    	try {
		//	this.init();
			switch(year.length)
			{
			   case 4:
				   this.sql=" select mpaltform,IFNULL(ciparea,'unknown'),chour,count(id) as dd from t_tj_pltfmactuser " +
	   			    "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' and cday='"+year[2]+"' ";
				   if(year[3]!=null && !year[3].equals(""))
					   sql = sql+" and mpaltform='"+year[3]+"' group by chour,ciparea order by mpaltform,chour,ciparea";
				   else
					   sql = sql+"group by chour,ciparea order by mpaltform,chour,ciparea";
				   break;
			   case 3:
				   this.sql=" select mpaltform,IFNULL(ciparea,'unknown'),cday,count(id) as dd from t_tj_pltfmactuser " +
			            "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' ";
				   if(year[2]!=null && !year[2].equals(""))
					   sql = sql+" and mpaltform='"+year[2]+"' group by ciparea,cday order by cday,ciparea";
				   else
					   sql = sql+"group by mpaltform,ciparea,cday order by mpaltform,chour,ciparea";
				   break;
			   case 2:
				   this.sql=" select mpaltform,IFNULL(ciparea,'unknown'),cmonth,count(id) as dd from t_tj_pltfmactuser " +
		            "  where cyear='"+year[0]+"' ";	
				   if(year[1]!=null && !year[1].equals(""))
					   sql = sql+" and mpaltform='"+year[1]+"' group by ciparea,cmonth order by cmonth,ciparea";
				   else
					   sql = sql+"group by mpaltform,ciparea,cmonth order by mpaltform,cmonth,ciparea";
				   break;
			   default:
				   break;
			}
			logger.info("findTTjPltfmActUser excute: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
	    	   temp=new TTjPltfmActUser();
	    	   temp.setMpaltform(this.getString(o[0]));
	    	   temp.setCiparea(this.getString(o[1]));
	    	   temp.setCmonth(this.getString(o[2]));
	    	   temp.setUsernumber(this.getInteger(o[3]));
	    	   t.add(temp);
	    	}    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
//    	finally{this.closeAll(); }
        return t;
    }
  //----------------------------0318 channle platform user------------------------
    public List<TTjPltfmActUser> findTTjChlfmActUser(String... year) {
    	List<TTjPltfmActUser> t=new ArrayList<TTjPltfmActUser>();
    	TTjPltfmActUser temp=null;
    	try {
		//	this.init();
			switch(year.length)
			{
			   case 4:
				   this.sql=" select mpaltform,IFNULL(magent,'no channel'),chour,count(id) as dd from t_tj_pltfmactuser " +
	   			    "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' and cday='"+year[2]+"' ";
				   if(year[3]!=null && !year[3].equals(""))
					   sql = sql+" and mpaltform='"+year[3]+"' group by chour,magent order by mpaltform,chour,magent";
				   else
					   sql = sql+"group by chour,magent order by mpaltform,chour,magent";
				   break;
			   case 3:
				   this.sql=" select mpaltform,IFNULL(magent,'no channel'),cday,count(id) as dd from t_tj_pltfmactuser " +
			            "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' ";
				   if(year[2]!=null && !year[2].equals(""))
					   sql = sql+" and mpaltform='"+year[2]+"' group by magent,cday order by cday,magent";
				   else
					   sql = sql+"group by mpaltform,magent,cday order by mpaltform,chour,magent";
				   break;
			   case 2:
				   this.sql=" select mpaltform,IFNULL(magent,'no channel'),cmonth,count(id) as dd from t_tj_pltfmactuser " +
		            "  where cyear='"+year[0]+"' ";	
				   if(year[1]!=null && !year[1].equals(""))
					   sql = sql+" and mpaltform='"+year[1]+"' group by magent,cmonth order by cmonth,magent";
				   else
					   sql = sql+"group by mpaltform,magent,cmonth order by mpaltform,cmonth,magent";
				   break;
			   default:
				   break;
			}
			logger.info("findTTjPltfmActUser excute: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
	    	   temp=new TTjPltfmActUser();
	    	   temp.setMpaltform(this.getString(o[0]));
	    	   temp.setCiparea(this.getString(o[1]));
	    	   temp.setCmonth(this.getString(o[2]));
	    	   temp.setUsernumber(this.getInteger(o[3]));
	    	   t.add(temp);
	    	}    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
    	
        return t;
    }
    
    public List<DataMap> getAllUserPlatform()
    {
    	this.sql = "SELECT distinct mpaltform FROM t_tj_pltfmactuser t";
    	try {
			return this.queryALLResultSet(sql);
         } catch (Exception e) {
         	logger.info(" getAllUserPlatform fail !!!" +e.getMessage());
            return null;
         }
    }
    
  //----------------------------0322 channel access------------------------
    public List<TTjChannelsAccess> findTTjChannelsAccess(String... year) {
    	List<TTjChannelsAccess> t=new ArrayList<TTjChannelsAccess>();
    	TTjChannelsAccess temp=null;
    	try {
		
			switch(year.length)
			{
			   case 2:
				   this.sql=" SELECT ch_name,cday,accessnumber FROM t_tj_channelsaccess t " +
	   			    "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' ";
				   break;
			   case 1:
				   this.sql=" SELECT ch_name,cmonth,sum(accessnumber) FROM t_tj_channelsaccess t " +
			            "  where cyear='"+year[0]+"' group by ch_name,cmonth";
				   break;
			   default:
				   break;
			}
			logger.info("findTTjChannelsAccess excute: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
	    	   temp=new TTjChannelsAccess();
	    	   temp.setCh_name(this.getString(o[0]));
	    	   temp.setCmonth(this.getString(o[1]));
	    	   temp.setAccessnumber(this.getInteger(o[2]));
	    	   t.add(temp);
	    	}    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
//    	finally{this.closeAll(); }
        return t;
    }
    
  //----------------------------0323 ad access------------------------
    public List<TTjAdsAccess> findTTjAdsAccess(String... year) {
    	List<TTjAdsAccess> t=new ArrayList<TTjAdsAccess>();
    	TTjAdsAccess temp=null;
    	try {
//			this.init();
			switch(year.length)
			{
			   case 2:
				   this.sql=" SELECT ad_name,cday,accessnumber FROM t_tj_adsaccess t " +
	   			    "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' ";
				   break;
			   case 1:
				   this.sql=" SELECT ad_name,cmonth,sum(accessnumber) FROM t_tj_adsaccess t " +
			            "  where cyear='"+year[0]+"' group by ad_name,cmonth";
				   break;
			   default:
				   break;
			}
			logger.info("findTTjAdsAccess excute: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
	    	   temp=new TTjAdsAccess();
	    	   temp.setAd_name(this.getString(o[0]));
	    	   temp.setCmonth(this.getString(o[1]));
	    	   temp.setAccessnumber(this.getInteger(o[2]));
	    	   t.add(temp);
	    	}    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
    
        return t;
    }
    
  //----------------------------0324 top twenty------------------------
    public List<TTjTopTwenty> findTTjTopTwenty(String... year) {
    	List<TTjTopTwenty> t=new ArrayList<TTjTopTwenty>();
    	TTjTopTwenty temp=null;
    	try {
			
			switch(year.length)
			{
			   case 2:
				   this.sql=" SELECT cyear,cmonth,contenttitle,contentsource,newstype,accessnumber FROM t_tj_toptwenty t " +
	   			    "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' order by accessnumber desc limit 20";
				   break;
			   case 1:
				   this.sql=" SELECT cyear,cmonth,contenttitle,contentsource,newstype,sum(accessnumber) number FROM t_tj_toptwenty t " +
			            "  where cyear='"+year[0]+"' group by contenttitle order by number desc limit 20";
				   break;
			   default:
				   break;
			}
			logger.info("findTTjTopTwenty excute: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
	    		temp=new TTjTopTwenty();
	    		temp.setCyear(this.getString(o[0])+InterUtil.rb.getString("src.year"));
	    		if(year.length==2)
	    			temp.setCyear(this.getString(o[0])+InterUtil.rb.getString("src.year")+
	    					this.getString(o[1])+InterUtil.rb.getString("src.month"));
	    		temp.setContenttitle(this.getString(o[2]));
	    		String source = this.getString(o[3]);
	    		
	    		temp.setContentsource(source);
	    		String newsType =this.getString(o[4]);
	    		if(newsType.equalsIgnoreCase("news"))          newsType=InterUtil.rb_jsp.getString("newspaper.news");
	    		if(newsType.equalsIgnoreCase("game"))          newsType=InterUtil.rb_jsp.getString("genre.game");
	    		if(newsType.equalsIgnoreCase("song"))          newsType=InterUtil.rb_jsp.getString("genre.song");
	    		if(newsType.equalsIgnoreCase("video"))         newsType=InterUtil.rb_jsp.getString("service.video");
	    		if(newsType.equalsIgnoreCase("ebook"))         newsType=InterUtil.rb_jsp.getString("service.e-book");
	    		if(newsType.equalsIgnoreCase("comic"))         newsType=InterUtil.rb_jsp.getString("ebook.type.comic");
	    		if(newsType.equalsIgnoreCase("magazine"))      newsType=InterUtil.rb_jsp.getString("ebook.type.magazine");
	    		if(newsType.equalsIgnoreCase("cpackage"))      newsType=InterUtil.rb_jsp.getString("service.package");
	    		if(newsType.equalsIgnoreCase("section"))       newsType=InterUtil.rb.getString("src.sectionForReport");
	    		if(newsType.equalsIgnoreCase("image"))         newsType=InterUtil.rb_jsp.getString("genre.image");
	    		if(newsType.equalsIgnoreCase("subject"))       newsType=InterUtil.rb_jsp.getString("service.theme");
	    		if(newsType.equalsIgnoreCase("ad"))            newsType=InterUtil.rb_jsp.getString("channel.ads");
	    		if(newsType.equalsIgnoreCase("question"))      newsType=InterUtil.rb_jsp.getString("channel.question");
	    		temp.setNewstype(newsType);
	    		temp.setAccessnumber(this.getInteger(o[5]));
	    		t.add(temp);
	    	}    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
    	
        return t;
    }
    
  //----------------------------0324 new user------------------------
    public List<TTjNewUser> findTTjNewUser(String... year) {
    	List<TTjNewUser> t=new ArrayList<TTjNewUser>();
    	TTjNewUser temp=null;
    	try {
		
			switch(year.length)
			{
			   case 3:
				   this.sql=" select mpaltform,IFNULL(magent,'no channel'),cday,count(id) as dd from t_tj_newuser " +
			            "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' ";
				   if(year[2]!=null && !year[2].equals(""))
					   sql = sql+" and mpaltform='"+year[2]+"' group by magent,cday order by cday,magent";
				   else
					   sql = sql+"group by mpaltform,magent,cday order by mpaltform,cday,magent";
				   break;
			   case 2:
				   this.sql=" select mpaltform,IFNULL(magent,'no channel'),cmonth,count(id) as dd from t_tj_newuser " +
		            "  where cyear='"+year[0]+"' ";	
				   if(year[1]!=null && !year[1].equals(""))
					   sql = sql+" and mpaltform='"+year[1]+"' group by magent,cmonth order by cmonth,magent";
				   else
					   sql = sql+"group by mpaltform,magent,cmonth order by mpaltform,cmonth,magent";
				   break;
			   default:
				   break;
			}
			logger.info("findTTjNewUser excute: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
	    	   temp=new TTjNewUser();
	    	   temp.setMpaltform( this.getString(o[0]));
	    	   String magent =this.getString(o[1]);
	    	 
	    	   temp.setMagent(magent);
	    	   temp.setCmonth(this.getString(o[2]));
	    	   temp.setUsernumber(this.getInteger(o[3]));
	    	   t.add(temp);
	    	}    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
    
        return t;
    }
    
    public List<DataMap> getAllUserPlatformForNewUser()
    {
    	this.sql = "SELECT distinct mpaltform FROM t_tj_newuser t";
    	try {
			return this.queryALLResultSet(sql);
         } catch (Exception e) {
         	logger.info(" getAllUserPlatformForNewUser fail !!!" +e.getMessage());
            return null;
         }
    }
    
    public List<TTjMobile> findMobile(String year) {
    	List<TTjMobile> t=new ArrayList<TTjMobile>();
    	TTjMobile temp=null;
    	try {
			
    	this.sql="  select  ifnull(uaname,'notset') as uaname  , ifnull(pricetype,'notset') as pricetype ,cmonth,sum(downloadnumber) as dd from t_tj_mobile " +
    			" where cyear='"+year+"' group by uaname,pricetype,cmonth order by uaname,pricetype,cmonth ";
    	List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
		for(Object[]o:tempp)
		{
    	   temp=new TTjMobile();
    	   temp.setUaname(this.getString(o[0]));
    	   temp.setPricetype(this.getString(o[1]));
    	   temp.setCmonth(this.getString(o[2]));
    	   temp.setDownloadnumber(this.getInteger(o[3]));
    	   t.add(temp);
    	}    	
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
        return t;
    }
    public List<TTjUsernumber> findUsernumber(String year) {
    	List<TTjUsernumber> t=new ArrayList<TTjUsernumber>();
    	TTjUsernumber temp=null;
    	try {
			
		
    	this.sql="  select  ifnull(sname,'notset') as sname , ifnull(pricetype,'notset') as pricetype ,cmonth,sum(downloadnumber) as dd from t_tj_usernumber " +
    			" where cyear='"+year+"' group by sname,pricetype,cmonth order by sname,pricetype,cmonth";
    	List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
		for(Object[]o:tempp)
		{
    	   temp=new TTjUsernumber();
    	   temp.setSname(this.getString(o[0]));
    	   temp.setPricetype(this.getString(o[1]));
    	   temp.setCmonth(this.getString(o[2]));
    	   temp.setDownloadnumber(this.getInteger(o[3])); 
    	   t.add(temp);
    	}    	
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
        return t;
    }
    public List<TTjInoutuser> findIOuser(String... year) {
    	List<TTjInoutuser> t=new ArrayList<TTjInoutuser>();
    	TTjInoutuser temp=null;
    	try {
			
		   switch(year.length)
		   {
		   case 3:
			   this.sql=" select  ifnull(sname,'notset') as sname , ifnull(inorout,'notset') as inorout ,chour,sum(usernumber) as dd from t_tj_inoutuser " +
   			            "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"' and cday='"+year[2]+"'   group by sname,inorout,chour order by sname,inorout,chour";
			   break;
		   case 2:
			   this.sql=" select ifnull(sname,'notset') as sname , ifnull(inorout,'notset') as inorout ,cday,sum(usernumber) as dd from t_tj_inoutuser " +
		            "  where cyear='"+year[0]+"' and cmonth='"+year[1]+"'   group by sname,inorout,cday order by sname,inorout,cday";
			   break;
		   case 1:
			   this.sql=" select  ifnull(sname,'notset') as sname , ifnull(inorout,'notset') as inorout ,cmonth,sum(usernumber) as dd from t_tj_inoutuser " +
	            "  where cyear='"+year[0]+"'   group by sname,inorout,cmonth order by sname,inorout,cmonth";		  
			   break;
		   default:
			   break;
		   
		   }
		   List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
		for(Object[]o:tempp)
		{
    	   temp=new TTjInoutuser();
    	   temp.setSname(this.getString(o[0]));
    	   temp.setInorout(this.getString(o[1]));
    	   temp.setCmonth(this.getString(o[2]));
    	   temp.setUsernumber(this.getInteger(o[3]));
    	   t.add(temp);
    	}    	
    	} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
        return t;
    }
    
    public List<DataMap> findNewsTjMenber(String... year) {
    		   String Rules="";
    		   if("internal".equals(year[(year.length-1)])){
    			   Rules=" and (ciparea like '中国%' or ciparea like '北京%' or ciparea like '天津%' or ciparea like '河北%' " +
			   		" or ciparea like '山西%' or ciparea like '内蒙古%' or ciparea like '辽宁%' or ciparea like '吉林%' " +
			   		" or ciparea like '黑龙江%' or ciparea like '上海%' or ciparea like '江苏%' or ciparea like '浙江%' " +
			   		" or ciparea like '安徽%' or ciparea like '福建%' or ciparea like '江西%' or ciparea like '山东%' " +
			   		" or ciparea like '河南%' or ciparea like '湖北%' or ciparea like '湖南%' or ciparea like '广东%' " +
			   		" or ciparea like '广西%' or ciparea like '海南%' or ciparea like '重庆%' or ciparea like '四川%' " +
			   		" or ciparea like '贵州%' or ciparea like '青海%' or ciparea like '宁夏%' or ciparea like '新疆%' " +
			   		" or ciparea like '甘肃%' or ciparea like '云南%' or ciparea like '西藏%' or ciparea like '陕西%' " +
			   		" or ciparea like '台湾%' or ciparea like '香港%' or ciparea like '澳门%') ";
    		   }else if("abroad".equals(year[(year.length-1)])){
    			   Rules=" and ciparea not like '中国%' and ciparea not like '北京%' and ciparea not like '天津%' and ciparea not like '河北%' " +
			   		" and ciparea not like '山西%' and ciparea not like '内蒙古%' and ciparea not like '辽宁%' and ciparea not like '吉林%' " +
			   		" and ciparea not like '黑龙江%' and ciparea not like '上海%' and ciparea not like '江苏%' and ciparea not like '浙江%' " +
			   		" and ciparea not like '安徽%' and ciparea not like '福建%' and ciparea not like '江西%' and ciparea not like '山东%' " +
			   		" and ciparea not like '河南%' and ciparea not like '湖北%' and ciparea not like '湖南%' and ciparea not like '广东%' " +
			   		" and ciparea not like '广西%' and ciparea not like '海南%' and ciparea not like '重庆%' and ciparea not like '四川%' " +
			   		" and ciparea not like '贵州%' and ciparea not like '青海%' and ciparea not like '宁夏%' and ciparea not like '新疆%' " +
			   		" and ciparea not like '甘肃%' and ciparea not like '云南%' and ciparea not like '西藏%' and ciparea not like '陕西%' " +
			   		" and ciparea not like '台湾%' and ciparea not like '香港%' and ciparea not like '澳门%' ";
    		   }else if("other".equals(year[(year.length-1)])){
    			   Rules=" and ciparea like 'Unknown' and ciparea like '[未知IP0801]'";
    		   }else if("province".equals(year[(year.length-1)])){
    			   Rules="";
    		   }else if(!"".equals(year[(year.length-1)])){
    			   //最后判断不为空时,为省市模糊查询
    			   Rules=" and ciparea like '"+year[(year.length-1)]+"%'";
    		   }
			   switch(year.length)
			   {
			   case 4:
				   this.sql ="select  ntj.ciparea ,ntj.cipcarrer," +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=0 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 0h," +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=1 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 1h," +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=2 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 2h," +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=3 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 3h," +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=4 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 4h," +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=5 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 5h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=6 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 6h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=7 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 7h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=8 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 8h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=9 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 9h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=10 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 10h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=11 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 11h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=12 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 12h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=13 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 13h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=14 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 14h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=15 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 15h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=16 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 16h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=17 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 17h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=18 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 18h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=19 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 19h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=20 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 20h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=21 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 21h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=22 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 22h, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=ntj.cday and chour=23 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 23h, " +
				   		" sum(cnumber) nn" +
				   		" from news_tj_menber ntj "+
				   		" where ntj.cyear="+year[0]+" and ntj.cmonth="+year[1]+" and ntj.cday="+year[2]+"" +Rules +
				   		" group by ntj.ciparea,ntj.cipcarrer order by ntj.ciparea ,nn desc";
				   break;
			   case 3:
				   this.sql = "select  ntj.ciparea ,ntj.cipcarrer," +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=1 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 1th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=2 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 2th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=3 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 3th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=4 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 4th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=5 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 5th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=6 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 6th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=7 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 7th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=8 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 8th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=9 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 9th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=10 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 10th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=11 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 11th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=12 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 12th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=13 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 13th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=14 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 14th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=15 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 15th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=16 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 16th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=17 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 17th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=18 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 18th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=19 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 19th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=20 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 20th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=21 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 21th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=22 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 22th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=23 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 23th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=24 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 24th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=25 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 25th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=26 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 26th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=27 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 27th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=28 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 28th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=29 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 29th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=30 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 30th, " +
				   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=ntj.cmonth and cday=31 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) 31th, " +
				   		" sum(cnumber) nn" +
				   		" from news_tj_menber ntj " +
				   		" where ntj.cyear="+year[0]+" and ntj.cmonth="+year[1]+"" +Rules+
				   		" group by ntj.ciparea,ntj.cipcarrer order by ntj.ciparea ,nn desc";
				   break;
			   case 2:
				   this.sql ="select ntj.ciparea ciparea,ntj.cipcarrer cipcarrer,"+
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=1 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Jan1," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=2 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Feb2," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=3 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Mar3," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=4 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Apr4," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=5 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) May5," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=6 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) June6,"+
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=7 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) July7,"+
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=8 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Aug8," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=9 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Sep9," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=10 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Oct10," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=11 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Nov11," +
			   		" ifnull((select sum(cnumber) from news_tj_menber where cyear=ntj.cyear and cmonth=12 and ciparea=ntj.ciparea and cipcarrer=ntj.cipcarrer),0) Dec12," +
			   		" sum(cnumber) nn"+
			   		" from news_tj_menber ntj"+
			   		" where ntj.cyear="+year[0]+""+Rules+
			   		" group by ntj.ciparea,ntj.cipcarrer order by ntj.ciparea ,nn desc";
				   break;
			   default:
				   break;
			   
			   }
	    	 try {
				return  queryALLResultSet(sql);
	         } catch (Exception e) {
	         	logger.info(" getPriceAll fail !!!" +e.getMessage());
	            return null;
	         }
	    }
    
    /**
     * 插入日志分析记录
     * @param dataList
     * @return
     * @throws Exception
     */
    public boolean insertAccesslog(List<String> dtimeList,List<String> ipList,List<String> udidList,Map<String,String> iparea_map,Map<String,String> ipcarrer_map){
    	boolean returnState = true;
    	try {
    		;
    		this.sql=" insert into news_accesslog (cyear,cmonth,cday,chour,cip,udid,ciparea,cipcarrer) values (?,?,?,?,?,?,?,?)";
    		Query query=em.createNativeQuery(sql);
//			query.setParameter(1, groupCatalog.getCatlogid());
	
    		int iii=0;
    		int bbb=0;
    		for(int i=0;i<dtimeList.size();i++){
    			query.setParameter(1, Integer.parseInt(dtimeList.get(i).substring(0, 4)));
    			query.setParameter(2, Integer.parseInt(dtimeList.get(i).substring(5, 7)));
    			query.setParameter(3, Integer.parseInt(dtimeList.get(i).substring(8, 10)));
    			query.setParameter(4, Integer.parseInt(dtimeList.get(i).substring(11, 13)));
    			query.setParameter(5, ipList.get(i));
    			query.setParameter(6, udidList.get(i));
    			query.setParameter(7, iparea_map.get(ipList.get(i)));
    			query.setParameter(8, ipcarrer_map.get(ipList.get(i)));
//    			prep.addBatch();
    			iii++;
    			query.executeUpdate();	
//    			if(++bbb%100==0){
//    				logger.info(bbb);
//    			}
//    			if(iii>100)
//    			{	
//    				prep.executeBatch();
//    				iii=0;
//    			}
    		}
//    		if(iii>0)
//			{	
//				prep.executeBatch();
//			}
    		
	    	}catch (Exception e) {
	            logger.debug("Error Occur.When execut the sql : "+sql);
	            e.printStackTrace();
	            returnState = false;
			}
	    	
	        return returnState;
	}
    
    /**
     * 更新日志分析统计  更新点击量
     * @return
     * @throws Exception 
     */
    public boolean updateTjMenber() throws Exception {
    	boolean returnState = true;
    	try {
    		
    		this.sql="REPLACE INTO news_tj_menber (cyear,cmonth,cday,chour,ciparea,cipcarrer,cnumber) " +
			"(SELECT cyear,cmonth,cday,chour,ciparea,cipcarrer, count(*) as cnumber FROM news_accesslog " +
			" WHERE ciparea is not null AND cipcarrer is not null" +
			" GROUP BY ciparea,cipcarrer,cyear,cmonth,cday,chour)";
    		/* 
    		    //news_tj_menber增加字段cudidnumber 记录活跃用户数
    		    this.sql="REPLACE INTO news_tj_menber (cyear,cmonth,cday,chour,ciparea,cipcarrer,cudidnumber,cnumber) " +
				"(SELECT cyear,cmonth,cday,chour,ciparea,cipcarrer,count(distinct udid) as cudidnumber, count(*) as cnumber FROM news_accesslog " +
				" WHERE ciparea is not null AND cipcarrer is not null" +
				" GROUP BY ciparea,cipcarrer,chour)";*/
//    		stmt.addBatch(sql);
//    		int[] iCodeArys=stmt.executeBatch();
//    		conn.commit();
    		this.em.createNativeQuery(sql).executeUpdate();
	    	} catch (Exception e) {
	    		//conn.rollback();
	            logger.debug("Error Occur.When execut the sql : "+sql);
	            returnState = false;
	            throw new Exception(e.getMessage());
    	}
    	
        return returnState;
		
	}
    
    
    /**
     * 删除三个月前的日志分析原始记录
     * @return
     */
    public boolean delExpiredData(String... year){
    	boolean returnState = true;
    	try {
    		;
    		this.sql=" delete from news_accesslog " +
    				" where cyear<'"+year[0]+"' or " +
    				" (cyear='"+year[0]+"' and cmonth<'"+year[1]+"') or " +
    				" (cyear='"+year[0]+"' and cmonth='"+year[1]+"' and cday<'"+year[2]+"') ";
    		this.em.createNativeQuery(sql).executeUpdate();
		} catch (Exception e) {
			returnState=false;
			logger.debug("delExpiredData faild!!!"+e.getMessage());
		}
    	return returnState;
    }
    
    
    /**
     * 得到所有需要更新 区域和运营商 的 ip
     * @return
     */
    public List<List<String>> findAllUpdateAccesslogIp() {
    	List<List<String>> temp=new ArrayList<List<String>>();
    	List<String> tempList=null;
    	try{
    		this.sql=" select id,cip from news_accesslog where ciparea is null and cipcarrer is null";
    		List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
    			tempList=new ArrayList<String>();
    			tempList.add(this.getString(o[0]));
    			tempList.add(this.getString(o[1]));
    			temp.add(tempList);
    		} 
    		logger.info("findAllUpdateAccesslogIp:"+temp.size());
    	}catch (Exception e) {
    		logger.debug(e.getMessage());
		}
 		return temp;
	}
    
    public boolean updateAccesslog(List<List<String>> tempList,
			Map<String, String> iparea_map, Map<String, String> ipcarrer_map) {
    	boolean returnState = true;
    	try {
    		;
    		this.sql=" update news_accesslog set ciparea=? ,cipcarrer=? where id=?";
    		Query query=em.createNativeQuery(sql);
//		(1, groupCatalog.getCatlogid());
			
    		int iii=0;
    		for(int i=0;i<tempList.size();i++){
    			query.setParameter(1, iparea_map.get(tempList.get(i).get(1)));
    			query.setParameter(2, ipcarrer_map.get(tempList.get(i).get(1)));
    			query.setParameter(3, Integer.parseInt(tempList.get(i).get(0)));
//    			prep.addBatch();
    			iii++;
//    			if(iii>100)
//    			{	
//    				prep.executeBatch();
//    				iii=0;
//    			}
    			query.executeUpdate();
    		}
//    		if(iii>0)
//			{	
//				prep.executeBatch();
//			}
    		
	    	}catch (Exception e) {
	            logger.debug("Error Occur.When execut the sql : "+sql);
	            e.printStackTrace();
	            returnState = false;
			}
	        return returnState;
	}
/*    public void save(Person person) {
        if (person.getId() == null) {
            // new
            em.persist(person);
        } else {
            // update
            em.merge(person);
        }
    }

    public void remove(int id) {
        Person person = find(id);
        if (person != null) {
            em.remove(person);
        }
    }
    public Person find(int id) {
        return em.find(Person.class, id);
    }*/
	
	
	
	
   

   

}